import traceback

def extract_metadata(conn_lock, conn, db_type):
  metadata = {}
  with conn_lock:
    cur = conn.cursor()
    if db_type=='SQL Server':
      cur.execute('SELECT name FROM sys.databases')
      databases = [ row[0].replace('"','') for row in cur ]
      print 'found sql server databases', databases
      for db in databases:
        print 'db', db
        try:
          cur.execute('''use "%s"; select table_catalog, table_name, column_name, is_nullable, data_type 
                         from information_schema.columns;'''%db)
          _load_mssql(metadata, cur)
          _pop_pks_mssql(metadata, cur)
        except:
          traceback.print_exc()
    elif db_type=='SQLite3':
      cur.execute("select name from sqlite_master where type = 'table';")
      tables = [ row[0].replace('(','').replace(')','') for row in cur ]
      print 'found tables', tables
      for table in tables:
        try:
          cur.execute('PRAGMA TABLE_INFO(%s);'%table)
          metadata = _load_sqlite3(cur,schema_name='schema',table_name=table)
        except:
          traceback.print_exc()
    elif db_type=='MySQL':
      metadata = _load_mysql(cur)
    else:
      cur.execute('select table_schema, table_name, column_name, is_nullable, data_type from information_schema.columns;')
      metadata = _load(cur)
    cur.close()
  return metadata
  

def _pop_pks_mssql(metadata, cur):
  cur.execute('''select a.table_catalog, a.table_name, b.column_name, a.constraint_name
                 from information_schema.table_constraints a,
                      information_schema.constraint_column_usage b
                 where constraint_type = 'PRIMARY KEY'
                       and a.constraint_name = b.constraint_name
                 order by a.table_name''')
  for row in cur:
    schema_name = row[0]
    table_name = row[1]
    column_name = row[2]
    schema = metadata.get(schema_name)
    if not schema: continue
    table = schema.get(table_name)
    if not table: continue
    column = table.get(column_name)
    if not column: continue
    column['primary_key'] = True


def _load(cur):
  metadata = {}
  for row in cur:
    schema_name = row[0]
    table_name = row[1]
    column_name = row[2]
    #print 'loading metadata...', row[1]
    schema = metadata.get(schema_name)
    if schema==None:
      metadata[schema_name] = schema = {}
    table = schema.get(table_name)
    if table==None:
      schema[table_name] = table = {}
    column = table.get(column_name)
    if column==None:
      table[column_name] = column = {}
    column['is_nullable'] = row[3] in ('YES',)
    column['datatype'] = row[4]
  return metadata


def _load_mssql(metadata, cur):
  for row in cur:
    schema_name = row[0]
    table_name = row[1]
    column_name = row[2]
    if schema_name=='dbo': continue
    if schema_name=='msdb': continue
    if table_name.startswith('syncobj_'): continue
    if table_name.startswith('MS'): continue
    #print 'loading metadata...', schema_name, table_name, column_name
    schema = metadata.get(schema_name)
    if schema==None:
      metadata[schema_name] = schema = {}
    table = schema.get(table_name)
    if table==None:
      schema[table_name] = table = {}
    column = table.get(column_name)
    if column==None:
      table[column_name] = column = {}
    column['is_nullable'] = row[3] in ('YES',)
    column['datatype'] = row[4]


def _load_mysql(cur):
  metadata = {}
  cur.execute('''select table_schema, table_name, column_name, is_nullable, data_type, column_key 
                 from information_schema.columns;''')
  for row in cur:
    schema_name = row[0]
    table_name = row[1]
    column_name = row[2]
    column_key = row[5]
    #print 'loading metadata...', row[1]
    schema = metadata.get(schema_name)
    if schema==None:
      metadata[schema_name] = schema = {}
    table = schema.get(table_name)
    if table==None:
      schema[table_name] = table = {}
    column = table.get(column_name)
    if column==None:
      table[column_name] = column = {}
    column['is_nullable'] = row[3] in ('YES',)
    column['datatype'] = row[4]
    if column_key=='PRI': 
      column['primary_key'] = True
  return metadata


def _load_sqlite3(cur, schema_name=None, table_name=None):
  metadata = {}
  for row in cur:
    column_name = row[1]
    #print 'loading metadata...', row[1]
    schema = metadata.get(schema_name)
    if schema==None:
      metadata[schema_name] = schema = {}
    table = schema.get(table_name)
    if table==None:
      schema[table_name] = table = {}
    column = table.get(column_name)
    if column==None:
      table[column_name] = column = {}
    column['is_nullable'] = True
    column['datatype'] = row[2]
  return metadata

